﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;


namespace Pub.DBUtility
{
    /// <summary>
    /// SQLLite 数据库 by chunhua.guo 2016-5-15
    /// </summary>
    internal class clsSQLLiteHelper : IDataBaseService
    {
        /// <summary>
        /// 主数据连接字符串
        /// </summary>
        private static string connectionString = "";
        /// <summary>
        /// 构造函数 分数据库
        /// </summary>
        /// <param name="p_strConnectPath">分数据库路径</param>
        public clsSQLLiteHelper(string p_strConnectPath)
        {
            //默认使用未加密
            connectionString = new clsGetConnectString().GetStringWithOutPwd(p_strConnectPath);
        }

        /// <summary>
        /// 构造函数，默认使用主数据库
        /// </summary>
        public clsSQLLiteHelper()
        {

        }
        #region 创建SQL参数
        /// <summary>
        /// 创建SQL参数
        /// </summary>
        /// <param name="num">参数个数</param>
        /// <param name="param"></param>
        public void CreateParameters(int num, out IDataParameter[] param)
        {
            param = null; param = new SQLiteParameter[num];
            for (int i = 0; i < num; i++)
            {
                param[i] = new SQLiteParameter();
                param[i].ParameterName = i.ToString();
            }
        }
        #endregion

        /// <summary>
        /// 转换SQL
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        private String strGetSQLServerPara(String strSQL)
        {
            int intPara = 0;
            for (int idx = strSQL.IndexOf("?"); idx > 0; idx = strSQL.IndexOf("?"))
            {
                strSQL = strSQL.Substring(0, idx) + "@" + (intPara++) + strSQL.Substring(idx + 1, strSQL.Length - idx - 1);
            }
            return strSQL;
        }

        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <param name="strDBName"></param>
        public void m_mthCreateDB(string strDBName)
        {
            SQLiteConnection.CreateFile(strDBName);
        }

        //public static long ExecSQL(string strSQL, string pwd)
        //{
        //    using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        //    {
        //        using (SQLiteCommand cmd = new SQLiteCommand(strSQL, connection))
        //        {
        //            try
        //            {
        //                connection.Open();
        //                int rows = cmd.ExecuteNonQuery();
        //                return rows;
        //            }
        //            catch (SQLiteException E)
        //            {
        //                connection.Close();
        //                throw new Exception(E.Message);
        //            }
        //        }
        //    }
        //}

        //public static void ChangePassword(string str)
        //{
        //    using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        //    {
        //        try
        //        {
        //            connection.Open();
        //            connection.ChangePassword(str);
        //        }
        //        catch (Exception e)
        //        {
        //            connection.Close();
        //        }
        //    }
        //}
        //public static void setPassword(string str)
        //{
        //    using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        //    {
        //        try
        //        {
        //            connection.Open();
        //            connection.SetPassword(str);
        //        }
        //        catch
        //        {
        //            connection.Close();
        //        }
        //    }
        //}


        /// <summary>
        /// 获取DataTable
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="dtResult"></param>
        /// <param name="Params"></param>
        /// <returns></returns>
        public long GetTableWithParams(string strSQL, ref DataTable dtResult, params IDataParameter[] Params)
        {
            strSQL = strGetSQLServerPara(strSQL);
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {

                using (SQLiteCommand cmd = new SQLiteCommand(strSQL, connection))
                {
                    if (Params.Length > 0)
                    {
                        cmd.Parameters.Add(Params);
                        //for (int i = 0; i < Params.Length; i++)
                        //{
                        //    ((SQLiteParameter)Params[i]).ParameterName = (i+! ).ToString();
                        //    cmd.Parameters.Add((SQLiteParameter)Params[i]);
                        //}
                    }
                    connection.Open();
                    dtResult = new DataTable();
                    SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                    da.Fill(dtResult);
                    connection.Close();
                    return 1;
                }

            }
        }

        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="AffectedCount"></param>
        /// <param name="Params"></param>
        /// <returns></returns>
        public long ExecParamSQL(string strSQL, ref long AffectedCount, params IDataParameter[] Params)
        {
            strSQL = strGetSQLServerPara(strSQL);
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(strSQL, connection))
                {
                    if (Params.Length > 0)
                    {
                        cmd.Parameters.AddRange(Params);
                        //for (int i = 0; i < Params.Length; i++)
                        //{
                        //    ((SQLiteParameter)Params[i]).ParameterName = (i + 1).ToString();
                        //    cmd.Parameters.Add((SQLiteParameter)Params[i]);
                        //}
                    }

                    connection.Open();
                    AffectedCount = cmd.ExecuteNonQuery();
                    connection.Close();
                    return AffectedCount;
                }
            }
        }

        /// <summary>
        /// 暂时没有实现此方法
        /// </summary>
        /// <param name="strProcName"></param>
        /// <param name="arrParams"></param>
        /// <returns></returns>
        public long ExecProc(string strProcName, IDataParameter[] arrParams)
        {
            throw new NotImplementedException();
        }
        /// <summary>
        /// 暂时没有实现此方法
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="Params"></param>
        /// <returns></returns>
        public long ExecParamProc(string strSQL, params IDataParameter[] Params)
        {
            throw new NotImplementedException();
        }
        /// <summary>
        /// 暂时没有实现此方法
        /// </summary>
        /// <param name="strProcName"></param>
        /// <param name="dtResult"></param>
        /// <returns></returns>
        public long GetTableFromProc(string strProcName, ref DataTable dtResult)
        {
            throw new NotImplementedException();
        }

        #region 执行多条SQL语句，实现数据库事务。
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>		
        public int ExecuteSqlTran(List<string> SQLStringList)
        {
            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = conn;
                SQLiteTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch
                {
                    tx.Rollback();
                    return 0;
                }
            }
        }


        #endregion

        public void m_mthChagePassword()
        {
            //生成密码 默认使用这个
            string strPwd = "Guoch";       
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                connection.ChangePassword(strPwd);

            }
        }
    }
}
